"""
https://openpyxl.readthedocs.io/en/stable/charts/bubble.html
"""
import openpyxl
from openpyxl.chart import BubbleChart, Reference, Series

# 创建工作簿
workbook = openpyxl.Workbook()
worksheet = workbook.active

worksheet.title = 'Bubble Chart'

# 准备数据
rows = [
    ("Number of Products", "Sales in USD", "Market share"),
    (14, 12200, 15),
    (20, 60000, 33),
    (18, 24400, 10),
    (22, 32000, 42),
    (),
    (12, 8200, 18),
    (15, 50000, 30),
    (19, 22400, 15),
    (25, 25000, 50),
]

for row in rows:
    worksheet.append(row)

# 绘制气泡图
chart = BubbleChart()
chart.style = 18

# 添加第一组数据
xValue = Reference(worksheet, min_col=1, min_row=2, max_row=5)
yValue = Reference(worksheet, min_col=2, min_row=2, max_row=5)
size = Reference(worksheet, min_col=3, min_row=2, max_row=5)
series = Series(values=yValue, xvalues=xValue, zvalues=size, title="2013")
chart.series.append(series)

# 添加第二组数据
xValue = Reference(worksheet, min_col=1, min_row=7, max_row=10)
yValue = Reference(worksheet, min_col=2, min_row=7, max_row=10)
size = Reference(worksheet, min_col=3, min_row=7, max_row=10)
series = Series(values=yValue, xvalues=xValue, zvalues=size, title="2014")
chart.series.append(series)

# 指定柱状图在Excel中的位置
worksheet.add_chart(chart, "H10")

# 保存工作簿
workbook.save('BubbleChart.xlsx')
